package ven.spots;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import android.util.Log;

public class DatabaseConnector {
	/* Filter DDMS logs by this tag to read messages from this class */
	private final String DDMS_FILTER_NAME = "database";
	
	/* Database login and location */
	private final String DB_USERNAME = "username";
	private final String DB_PASSWORD = "password";
	private final String DB_URL      = "jdbc:mysql://localhost/test";
	
	private Connection connection;
	
	/* Table names in database */
	private final String TBL_NODES_NAME = "tbl_nodes";
	private final String TBL_DATA_NAME  = "tbl_data";
	
	/* Query for selecting all nodes from TBL_NODES_NAME */
	private final String SELECT_NODES = "SELECT * FROM " + TBL_NODES_NAME;
	
	/* Query for selecting data from specific spot nodes */
	private final String SELECT_DATA = "SELECT * FROM " + TBL_DATA_NAME
									 + " WHERE mac=?";
	
	/* Query for updating the color values for a given node in the database */
	private final String UPDATE_NODE = "UPDATE " + TBL_NODES_NAME + " SET "
									 + "red=?, green=?, blue=? WHERE mac=?";
	
	
	/**
	 * Constructs DatabaseConnector object and initializes
	 * connection to a database.
	 */
	public DatabaseConnector() {
		try {
			Class.forName ("com.mysql.jdbc.Driver").newInstance();
			connection = DriverManager.getConnection(DB_URL, 
													 DB_USERNAME,
													 DB_PASSWORD);
			connection.setAutoCommit(true);
		} catch (Exception e) {
			Log.d(DDMS_FILTER_NAME, e.getMessage());
		}
	}
	
	/**
	 * Close any open connections to the database.
	 */
	public void closeDatabase() {
		try {
			connection.close();
		} catch(Exception e) {
			Log.d(DDMS_FILTER_NAME, e.getMessage());
		}
	}
		
	/**
	 * Grab all nodes from the database and dump them into an arraylist
	 */
	public ArrayList<SpotNode> getNodes() {
		ArrayList<SpotNode> nodes = new ArrayList<SpotNode>();
		try {
			Statement statement = connection.createStatement();
			statement.executeQuery(SELECT_NODES);
			ResultSet rs = statement.getResultSet();
			while(rs.next()) {
				//Grab data values from the database
				// and assign them to java vars
				String ieee = rs.getString("mac");
				int state   = rs.getInt("state");
				
				//Make sure the data from the db is not
				//null and throw it in the nodes array
				if (rs.wasNull() != true) {
					nodes.add(new SpotNode(ieee, state));
				}
			rs.close();
			statement.close();
			}
		} catch (Exception e) {
			Log.d(DDMS_FILTER_NAME, e.getMessage());
		}
		return nodes;
	}

	/**
	 * Pull data from the database for a specific node.
	 * @param node SpotNode object representing a node.  This 
	 * 		  method will pull this node's data from the database.
	 * @return ArrayList of SpotData objects representing data 
	 * 		   gathered by param 'node'
	 */
	public ArrayList<SpotData> getData(SpotNode node) {
		ArrayList<SpotData> data = new ArrayList<SpotData>();
		try {
			PreparedStatement statement;
			statement = connection.prepareStatement(SELECT_DATA);
			statement.setString(1, "mac");
			ResultSet rs = statement.executeQuery();
			while (rs.next() != true) {
				String mac = rs.getString("mac");
				Date  date = rs.getDate("dtime");
				int  light = rs.getInt("light");
				int  state = rs.getInt("state");
				int    red = rs.getInt("red");
				int  green = rs.getInt("green");
				int   blue = rs.getInt("blue");
				if (rs.wasNull() != true) {
					SpotData sd = new SpotData();
					sd.setIeee(mac);
					sd.setDate(date);
					sd.setLight(light);
					sd.setState(state);
					sd.setRed(red);
					sd.setGreen(green);
					sd.setBlue(blue);
					data.add(sd);
				}
			}
			rs.close();
			statement.close();
		} catch (Exception e) {
			Log.d(DDMS_FILTER_NAME, e.getMessage());
		}
		return data;
	}
	
	/**
	 * Set the color for a spot to flash in the database. 
	 * @param mac - Mac address of the spot to set color for.
	 * @param r - int 0-255 representing red
	 * @param g - int 0-255 representing green
	 * @param b - int 0-255 representing blue
	 */
	public void setFlashColor(String mac, int r, int g, int b) {
		try {
			PreparedStatement statement;
			statement = connection.prepareStatement(UPDATE_NODE);
			statement.setInt(1, r);
			statement.setInt(2, g);
			statement.setInt(3, b);
			statement.setString(4, mac);
			statement.executeUpdate();
			statement.close();
		} catch (Exception e) {
			Log.d(DDMS_FILTER_NAME, e.getMessage());
		}
	}
}